# -*- coding:utf-8 -*-
import sys
import pymysql.cursors
from cn.sys.home.utils.ConfigUtils import ReadConfig
from cn.sys.home.utils.AnalysisDBScript import DBScript
class MySQLPool():
    @staticmethod
    def getConnection():
        dbCondif = ReadConfig.readConfigByPath("/cn/sys/home/conf/db.ini")
        connection = pymysql.connect(host=dbCondif.host,
                             user=dbCondif.user,
                             password=dbCondif.password,
                             db=dbCondif.db,
                             charset=dbCondif.charset,
                             cursorclass=pymysql.cursors.DictCursor)
        cursor = connection.cursor()
        cursor.execute("SET NAMES utf8")
        return connection,cursor

    @staticmethod
    def close(conn, cur):
        cur.close()
        conn.close()



class MysqlDDL():

    show_db_version = "SELECT VERSION()"
    show_db_tables = "show tables"
    show_db_charset = "show variables like 'character_set_%';"
    character_set_database="set @@character_set_database=utf8"
    character_set_server="set @@character_set_server=utf8"

    @staticmethod
    def showDataBaseInfo(dbname):
        conn, cur = MySQLPool.getConnection()
        cur.execute(MysqlDDL.show_db_version)
        db_version = cur.fetchone()
        print(dbname,db_version["VERSION()"])
        cur.execute(MysqlDDL.show_db_charset)
        charsets = cur.fetchall()
        print("databse charset info",charsets)
        cur.execute(MysqlDDL.show_db_tables)
        return cur.fetchall()

    @staticmethod
    def getTableInfoByName(name):
        conn, cur = MySQLPool.getConnection()
        cur.execute("show full fields  from "+name)
        fields = cur.fetchall()
        return name,fields



if __name__=='__main__':
    # 读取脚本
    db = ReadConfig.readDBScriptByPath("/cn/sys/home/script/db.sql")
    #解析脚本  将解析好的数据解构可以很好的跟数据库表解构进行对比
    DBScript.extractTableScriptFromScript("ads")
    # 查询库中所有数据表
    tables=MysqlDDL.showDataBaseInfo("125home")
    # for table in tables:
        # 获得每个表中的字段属性
        # tablename,fields=MysqlDDL.getTableInfoByName(table['Tables_in_125home_2'])


class MysqlDML():

    @staticmethod
    def executSQL(sql):
        conn,cur=MySQLPool.getConnection()
        try:
            cur.execute(sql)
            conn.commit()
        except:
            conn.rollback()
        finally:
            MySQLPool.close(conn,cur)

    @staticmethod
    def executeQueryOne(sql):
        conn, cur = MySQLPool.getConnection()
        try:
            cur.execute(sql)
            result = cur.fetchone()
        finally:
            MySQLPool.close(conn, cur)
        return result

    @staticmethod
    def executQueryAll(sql):
        conn, cur = MySQLPool.getConnection()
        try:
            cur.execute(sql)
            results = cur.fetchall()
            for row in results:
                print( row)
        finally:
            MySQLPool.close(conn, cur)
        return results




# if __name__=='__main__':
    # MysqlDML.executQueryAll("select  uid,username,nickname from user")
    # MysqlDDL.getTableInfoByName("user")